Self Joins and Joining a Table to Itself in MySQL

Course: MySQL Training

Topic: Self Joins and Joining a Table to Itself

Course Link: MySQL Training Course

Demo Link: Register for a Free Course Demo

1. Introduction to Self Joins in MySQL

A self join is a SQL technique in which a table is joined with itself to compare records or establish relationships between rows in the same table. It allows you to retrieve related information stored in different rows without requiring a separate table.

For example, a company may store employee information in a single employees table. Each employee has an employee ID and may also have a manager ID. The manager is another employee in the same table. A self join can combine these records to display each employee's name alongside their manager's name.

MySQL does not provide a separate SELF JOIN keyword. Instead, a self join is written using a regular join, such as INNER JOIN or LEFT JOIN, with two different aliases for the same table. MySQL JOIN documentation.

1.1 What Is a Self Join?

A self join references the same database table more than once in a query. Each reference is assigned a different alias so MySQL can distinguish the role played by each reference.

Consider an employee table containing the following information:

In this table, Priya is the manager of Rahul and Amit, while Rahul is the manager of Neha. The manager_id column stores the employee ID of the manager.

A self join allows us to match the manager_id of one employee with the employee_id of another row in the same table.

1.2 Why Is a Self Join Needed?

• To display employee and manager names from the same table.
• To identify customers who live in the same city.
• To compare products with other products in the same category.
• To find records with matching or related values.
• To identify duplicate or potentially duplicate records.
• To compare dates, prices, salaries, or other values across rows.
• To analyze hierarchical relationships such as employees, departments, and organizational structures.

2. Learning Objectives

After completing this topic, learners should be able to:

• Understand the meaning and purpose of a self join.
• Explain why table aliases are required or strongly recommended.
• Write self joins using INNER JOIN and LEFT JOIN.
• Display employees alongside their managers.
• Compare records within the same table.
• Find customers or products sharing common attributes.
• Use filtering, sorting, and aggregation with self joins.
• Recognize duplicate pairs and avoid unnecessary repeated results.
• Identify common errors and improve self-join query performance.

3. Understanding Table Aliases

A table alias is a temporary name assigned to a table reference within a SQL statement. In a self join, two aliases represent two roles played by the same physical table.

3.1 Why Are Aliases Important?

Suppose a query refers to the employees table twice. Without distinguishing the references, it can be difficult to specify which employee's ID, name, or manager ID should be used in a condition.

We can assign the aliases e and m:

• e represents the employee whose details we want to display.
• m represents the manager associated with that employee.

SELECT
    e.employee_name AS employee_name,
    m.employee_name AS manager_name
FROM employees AS e
INNER JOIN employees AS m
    ON e.manager_id = m.employee_id;

Both aliases reference the same table. However, each alias is used for a different purpose in the query.

3.2 General Self-Join Syntax

SELECT
    alias1.column_name,
    alias2.column_name
FROM table_name AS alias1
INNER JOIN table_name AS alias2
    ON alias1.related_column = alias2.matching_column;

In this syntax:

• table_name is the table being referenced twice.
• alias1 is the first reference to the table.
• alias2 is the second reference to the same table.
• ON specifies the condition that connects the rows.
• SELECT identifies the columns to return.

3.3 Naming Aliases Clearly

Short aliases are common, but meaningful aliases such as employee and manager may improve readability in larger queries.

4. Creating a Sample Employees Table

Before writing self joins, create a sample table that stores employee details and the ID of each employee's manager.

4.1 Create the Table

CREATE TABLE employees (
    employee_id INT PRIMARY KEY,
    employee_name VARCHAR(100) NOT NULL,
    department VARCHAR(50),
    salary DECIMAL(10,2),
    manager_id INT NULL
);

The table contains five columns:

4.2 Insert Sample Records

INSERT INTO employees
(employee_id, employee_name, department, salary, manager_id)
VALUES
(1, 'Priya', 'Management', 90000, NULL),
(2, 'Rahul', 'IT', 65000, 1),
(3, 'Amit', 'IT', 60000, 1),
(4, 'Neha', 'Sales', 55000, 2),
(5, 'Karan', 'Sales', 50000, 2),
(6, 'Meera', 'HR', 58000, 1);

4.3 View the Table

SELECT *
FROM employees
ORDER BY employee_id;

Expected data:

5. Performing a Self Join Using INNER JOIN

An INNER JOIN returns only the rows for which the join condition finds a match. In an employee-manager self join, employees without a matching manager record are excluded.

5.1 Display Employees and Their Managers

SELECT
    e.employee_id,
    e.employee_name AS employee_name,
    m.employee_name AS manager_name
FROM employees AS e
INNER JOIN employees AS m
    ON e.manager_id = m.employee_id
ORDER BY e.employee_id;

Expected output:

5.2 How Does the Query Work?

1. FROM employees AS e identifies the employee reference.
2. INNER JOIN employees AS m creates a second reference representing the manager.
3. ON e.manager_id = m.employee_id matches an employee's manager ID with the corresponding employee ID.
4. The SELECT statement returns the employee's name and the matched manager's name.
5. ORDER BY e.employee_id sorts the result by employee ID.

Priya does not appear as an employee in this output because her manager_id is NULL, so no matching manager row satisfies the join condition for her record.

5.3 Selecting Additional Columns

SELECT
    e.employee_name AS employee_name,
    e.department AS employee_department,
    e.salary AS employee_salary,
    m.employee_name AS manager_name,
    m.department AS manager_department
FROM employees AS e
INNER JOIN employees AS m
    ON e.manager_id = m.employee_id;

This query displays the department and salary of the employee alongside the manager's name and department. Selecting columns with clear aliases makes the output easier to understand.

6. Performing a Self Join Using LEFT JOIN

A LEFT JOIN keeps every row from the first table reference and adds matching values from the second reference. When no match exists, columns from the second reference contain NULL.

6.1 Display All Employees, Including Employees Without Managers

SELECT
    e.employee_id,
    e.employee_name AS employee_name,
    m.employee_name AS manager_name
FROM employees AS e
LEFT JOIN employees AS m
    ON e.manager_id = m.employee_id
ORDER BY e.employee_id;

Expected output:

6.2 Replace NULL With a Descriptive Value

When an employee has no manager, the result may be easier to read if it displays a label instead of NULL. The COALESCE() function returns the first non-NULL expression.

SELECT
    e.employee_name AS employee_name,
    COALESCE(m.employee_name, 'No Manager') AS manager_name
FROM employees AS e
LEFT JOIN employees AS m
    ON e.manager_id = m.employee_id
ORDER BY e.employee_id;

For Priya, the manager name will be displayed as No Manager. This changes the displayed value only; it does not update the underlying table.

6.3 INNER JOIN vs LEFT JOIN in a Self Join

7. Understanding the Self-Join Condition

The ON condition is the most important part of a self join. It defines how a row from the first reference relates to a row from the second reference.

7.1 Matching a Foreign-Key-Like Column With a Primary Key

ON e.manager_id = m.employee_id

This condition matches the employee's manager ID with the manager's employee ID. Although both columns contain employee identifiers, they represent different roles in the relationship.

7.2 Matching Rows Using the Same Attribute

A self join can also compare rows based on a shared attribute such as city or department.

SELECT
    e1.employee_name AS employee_one,
    e2.employee_name AS employee_two,
    e1.department
FROM employees AS e1
INNER JOIN employees AS e2
    ON e1.department = e2.department
   AND e1.employee_id < e2.employee_id
ORDER BY e1.department, e1.employee_id;

This query finds pairs of employees who belong to the same department. The additional condition e1.employee_id < e2.employee_id prevents each pair from appearing in both directions and excludes matching an employee with themselves.

7.3 Using Multiple Join Conditions

Multiple conditions can be combined using AND or OR. For example, to find employees in the same department whose salaries differ:

SELECT
    e1.employee_name AS employee_one,
    e2.employee_name AS employee_two,
    e1.department,
    e1.salary AS salary_one,
    e2.salary AS salary_two
FROM employees AS e1
INNER JOIN employees AS e2
    ON e1.department = e2.department
   AND e1.employee_id < e2.employee_id
   AND e1.salary <> e2.salary;

Only pairs satisfying all the conditions are returned. If two employees in the same department have equal salaries, their pair is excluded by the final condition.

8. Finding Employees in the Same Department

Self joins can compare employees who share a common department. This is useful when preparing team lists, identifying possible collaborators, or analyzing department membership.

8.1 Find All Employee Pairs in the Same Department

SELECT
    e1.employee_name AS employee_one,
    e2.employee_name AS employee_two,
    e1.department
FROM employees AS e1
INNER JOIN employees AS e2
    ON e1.department = e2.department
   AND e1.employee_id < e2.employee_id
ORDER BY e1.department, e1.employee_name;

The query returns each unique pair of employees within the same department, provided the department values match. NULL department values do not match one another using ordinary equality.

8.2 Find Employees in the Same Department With Different Salaries

SELECT
    e1.employee_name AS employee_one,
    e2.employee_name AS employee_two,
    e1.department,
    e1.salary AS salary_one,
    e2.salary AS salary_two
FROM employees AS e1
JOIN employees AS e2
    ON e1.department = e2.department
   AND e1.employee_id < e2.employee_id
WHERE e1.salary <> e2.salary
ORDER BY e1.department;

This query is useful when you want to compare compensation between employees in the same department. Ensure that such comparisons follow your organization's privacy and access policies.

9. Finding Customers From the Same City

Self joins are not limited to employee tables. A customer table can also be joined to itself to find customers who share the same city.

9.1 Create a Customers Table

CREATE TABLE customers (
    customer_id INT PRIMARY KEY,
    customer_name VARCHAR(100) NOT NULL,
    city VARCHAR(100),
    country VARCHAR(100)
);

9.2 Insert Sample Customer Records

INSERT INTO customers
(customer_id, customer_name, city, country)
VALUES
(1, 'Aarav', 'Mumbai', 'India'),
(2, 'Isha', 'Mumbai', 'India'),
(3, 'Kabir', 'Pune', 'India'),
(4, 'Meera', 'Mumbai', 'India'),
(5, 'Rohan', 'Pune', 'India'),
(6, 'Sara', 'Delhi', 'India');

9.3 Find Customer Pairs in the Same City

SELECT
    c1.customer_name AS customer_one,
    c2.customer_name AS customer_two,
    c1.city
FROM customers AS c1
INNER JOIN customers AS c2
    ON c1.city = c2.city
   AND c1.customer_id < c2.customer_id
ORDER BY c1.city, c1.customer_name;

Expected output:

Delhi does not appear because only one customer is listed in that city, so no pair can be formed.

9.4 Why Use customer_id < customer_id?

Without a condition that distinguishes the two records, the query may return a customer paired with themselves and may return the same pair in reverse order.

For example, Aarav and Isha could appear as both Aarav–Isha and Isha–Aarav. The condition c1.customer_id < c2.customer_id ensures that each pair appears only once, assuming customer IDs are unique and non-NULL.

10. Comparing Products Within the Same Table

A product catalog often stores each product's category and price in one table. A self join can compare products in the same category or identify products whose prices are close to each other.

10.1 Example Product Table

CREATE TABLE products (
    product_id INT PRIMARY KEY,
    product_name VARCHAR(100) NOT NULL,
    category VARCHAR(50),
    price DECIMAL(10,2)
);

INSERT INTO products
(product_id, product_name, category, price)
VALUES
(1, 'Basic Keyboard', 'Accessories', 800),
(2, 'Wireless Keyboard', 'Accessories', 1500),
(3, 'Office Mouse', 'Accessories', 600),
(4, 'Laptop A', 'Computers', 45000),
(5, 'Laptop B', 'Computers', 52000);

10.2 Compare Products in the Same Category

SELECT
    p1.product_name AS product_one,
    p2.product_name AS product_two,
    p1.category,
    p1.price AS price_one,
    p2.price AS price_two
FROM products AS p1
INNER JOIN products AS p2
    ON p1.category = p2.category
   AND p1.product_id < p2.product_id
ORDER BY p1.category;

This query returns product pairs that belong to the same category. The ID condition prevents self-matching and reversed duplicate pairs.

10.3 Find More Expensive Products in the Same Category

SELECT
    p1.product_name AS lower_priced_product,
    p1.price AS lower_price,
    p2.product_name AS higher_priced_product,
    p2.price AS higher_price,
    p1.category
FROM products AS p1
INNER JOIN products AS p2
    ON p1.category = p2.category
   AND p1.price < p2.price
ORDER BY p1.category, p1.price;

This query returns ordered product comparisons where the second product is more expensive than the first. Unlike a condition based only on product IDs, the price condition may return several comparisons for one product.

11. Self Joins With WHERE Conditions

The WHERE clause filters the rows produced by the join. It can be used to restrict results by department, salary, city, or other attributes.

11.1 Find Employees Managed by Priya

SELECT
    e.employee_name AS employee_name,
    m.employee_name AS manager_name
FROM employees AS e
INNER JOIN employees AS m
    ON e.manager_id = m.employee_id
WHERE m.employee_name = 'Priya';

This query first matches employees with managers and then returns only employees whose manager is Priya.

11.2 Find Employees Whose Salary Is Higher Than Their Manager's

SELECT
    e.employee_name AS employee_name,
    e.salary AS employee_salary,
    m.employee_name AS manager_name,
    m.salary AS manager_salary
FROM employees AS e
INNER JOIN employees AS m
    ON e.manager_id = m.employee_id
WHERE e.salary > m.salary;

This example compares two salary values from different rows of the same table. In the sample data, no employee has a salary greater than their direct manager's salary, so the result is empty. An empty result can be correct when no row satisfies the condition.

11.3 Filter by Department

SELECT
    e.employee_name AS employee_name,
    m.employee_name AS manager_name,
    e.department
FROM employees AS e
LEFT JOIN employees AS m
    ON e.manager_id = m.employee_id
WHERE e.department = 'IT'
ORDER BY e.employee_name;

The query retains employees in the IT department and shows their manager where a match exists.

12. Self Joins With ORDER BY and LIMIT

ORDER BY sorts the result set, while LIMIT restricts how many rows are returned. They can help produce concise reports from self-join queries.

12.1 Sort Employees by Manager and Name

SELECT
    e.employee_name AS employee_name,
    m.employee_name AS manager_name
FROM employees AS e
LEFT JOIN employees AS m
    ON e.manager_id = m.employee_id
ORDER BY manager_name, employee_name;

12.2 Return Only the First Three Results

SELECT
    e.employee_name AS employee_name,
    m.employee_name AS manager_name
FROM employees AS e
LEFT JOIN employees AS m
    ON e.manager_id = m.employee_id
ORDER BY e.employee_id
LIMIT 3;

Because the query uses an explicit ordering, the first three records are selected according to employee ID. Without a suitable ORDER BY, the returned subset should not be treated as having a guaranteed order.

13. Self Joins With Aggregate Functions

Aggregate functions such as COUNT(), MIN(), MAX(), and AVG() can be combined with self joins to summarize relationships between rows.

13.1 Count Direct Reports for Each Manager

SELECT
    m.employee_id AS manager_id,
    m.employee_name AS manager_name,
    COUNT(e.employee_id) AS direct_report_count
FROM employees AS m
LEFT JOIN employees AS e
    ON e.manager_id = m.employee_id
GROUP BY m.employee_id, m.employee_name
ORDER BY direct_report_count DESC, m.employee_name;

The query uses the manager reference as the starting point and counts employees who report directly to each manager. The LEFT JOIN ensures that employees with no direct reports can still appear with a count of zero.

13.2 Calculate the Average Salary of Direct Reports

SELECT
    m.employee_name AS manager_name,
    AVG(e.salary) AS average_direct_report_salary,
    COUNT(e.employee_id) AS direct_report_count
FROM employees AS m
LEFT JOIN employees AS e
    ON e.manager_id = m.employee_id
GROUP BY m.employee_id, m.employee_name
ORDER BY m.employee_name;

AVG() ignores NULL salary values. If a manager has no direct reports, the average is NULL and the count is zero.

13.3 Important Grouping Rule

When grouping a self-join result, include the identifying columns that define each group. Grouping only by a non-unique name can combine separate employees who happen to share the same name. Grouping by the employee ID and name is safer.

14. Finding Duplicate or Similar Records

Self joins can help detect records that share attributes. They are useful for identifying possible duplicate customers, repeated contact details, or other data-quality issues.

14.1 Find Customers With the Same City and Country

SELECT
    c1.customer_id AS customer_id_one,
    c1.customer_name AS customer_one,
    c2.customer_id AS customer_id_two,
    c2.customer_name AS customer_two,
    c1.city,
    c1.country
FROM customers AS c1
INNER JOIN customers AS c2
    ON c1.city = c2.city
   AND c1.country = c2.country
   AND c1.customer_id < c2.customer_id
ORDER BY c1.city;

This identifies customer pairs sharing a city and country. It does not prove that the customers are duplicates; they may be different people living in the same location.

14.2 Find Records With Matching Email Addresses

If a table contains email addresses, a self join can identify repeated non-NULL email values.

SELECT
    a.customer_id AS customer_id_one,
    b.customer_id AS customer_id_two,
    a.email
FROM customer_contacts AS a
INNER JOIN customer_contacts AS b
    ON a.email = b.email
   AND a.customer_id < b.customer_id
WHERE a.email IS NOT NULL;

This example assumes a table named customer_contacts with columns customer_id and email. It will need to be adapted to the actual table structure. Matching email values should be reviewed before merging records or deleting data.

15. Self Joins With Comparison Operators

The join condition can use operators other than equality. These operators make it possible to compare numerical values, dates, and other compatible data types.

15.1 Compare Records by Date

Suppose a table named orders contains an order ID, customer ID, and order date. A self join can find pairs of orders placed by the same customer on different dates.

SELECT
    o1.customer_id,
    o1.order_id AS earlier_order_id,
    o1.order_date AS earlier_order_date,
    o2.order_id AS later_order_id,
    o2.order_date AS later_order_date
FROM orders AS o1
INNER JOIN orders AS o2
    ON o1.customer_id = o2.customer_id
   AND o1.order_date < o2.order_date
ORDER BY o1.customer_id, o1.order_date;

This query can return multiple order pairs for one customer. If multiple orders have the same timestamp or date, use a unique order ID as an additional tie-breaker when the goal is to create a deterministic ordering of pairs.

16. Using Multiple Self Joins

Sometimes a query needs to connect three or more roles from the same table. This can be done by referencing the table multiple times and assigning a unique alias to every reference.

16.1 Employee, Manager, and Senior Manager

Assume an employee reports to a manager, who may also report to another manager. The following query displays an employee, their direct manager, and their manager's manager.

SELECT
    e.employee_name AS employee_name,
    m.employee_name AS direct_manager,
    sm.employee_name AS senior_manager
FROM employees AS e
LEFT JOIN employees AS m
    ON e.manager_id = m.employee_id
LEFT JOIN employees AS sm
    ON m.manager_id = sm.employee_id
ORDER BY e.employee_id;

Here:

• e represents the employee.
• m represents the direct manager.
• sm represents the senior manager.

Both joins use LEFT JOIN, so employees are preserved even when one or both manager levels are missing. This query displays only two manager levels; it does not automatically retrieve an entire hierarchy of arbitrary depth.

17. Self Joins and Hierarchical Data

Hierarchical data represents parent-child relationships. Examples include employee-manager structures, categories and subcategories, organizational charts, and comments with parent comments.

17.1 How a Hierarchy Is Stored

A common design stores each record's identifier and a column referencing its parent record. In an employee table, the employee ID identifies a row, while the manager ID points to another employee.

17.2 Find the Direct Parent of a Record

SELECT
    child.employee_name AS child_name,
    parent.employee_name AS parent_name
FROM employees AS child
LEFT JOIN employees AS parent
    ON child.manager_id = parent.employee_id;

This returns each employee and their direct manager. The same pattern can be adapted to categories, departments, and other parent-child tables.

17.3 Limitations of Self Joins for Hierarchies

• A self join is suitable for one relationship level at a time.
• Additional fixed-depth levels require additional aliases and joins.
• Deep or unknown-depth hierarchies may require recursive common table expressions where supported by the MySQL version, or a different hierarchical data model.
• Care is needed to prevent circular references, such as an employee being assigned as their own manager.

18. Self Join vs Other Join Types

A self join describes the use of the same table more than once; it is not a separate join operator. The join type determines which row matches are preserved.

18.1 Self Join vs Joining Two Different Tables

19. Common Mistakes in Self Joins

19.1 Forgetting to Use Different Aliases

Using separate aliases makes it clear which role each column plays. Without aliases, references can be ambiguous or the query may not express the intended relationship correctly.

Recommended approach:

SELECT
    e.employee_name,
    m.employee_name AS manager_name
FROM employees AS e
JOIN employees AS m
    ON e.manager_id = m.employee_id;

19.2 Using the Wrong Join Condition

A condition such as e.employee_id = m.employee_id matches each employee with the same row rather than with their manager. Use the columns that represent the intended relationship.

-- Intended employee-manager relationship
ON e.manager_id = m.employee_id

19.3 Forgetting to Exclude the Same Record

When comparing rows with a shared attribute, an equality-only join may match each row with itself. Use an appropriate condition such as e1.employee_id <> e2.employee_id to exclude self-matches.

When the goal is to return each unordered pair exactly once, a condition such as e1.employee_id < e2.employee_id is usually better because it also prevents reversed duplicate pairs.

19.4 Producing Too Many Rows

A self join can produce many row combinations, especially when the join condition is broad or missing. If a table has 100 rows, a cross join can produce 10,000 combinations before filtering.

Solution: Use an appropriate ON condition and verify that the result represents the intended relationships.

19.5 Using INNER JOIN When Unmatched Rows Are Required

An INNER JOIN excludes rows without a matching record. If the report must include employees without managers, use LEFT JOIN.

19.6 Confusing ON and WHERE Conditions

In an outer self join, filtering the right-hand alias in the WHERE clause can remove rows where the right-hand columns are NULL, making the result behave like an inner join for that condition.

-- May exclude employees with no manager
SELECT e.employee_name, m.employee_name AS manager_name
FROM employees AS e
LEFT JOIN employees AS m
    ON e.manager_id = m.employee_id
WHERE m.employee_name = 'Priya';

If the goal is to preserve every employee while matching a manager only when the manager is Priya, put the condition in the join clause instead:

SELECT
    e.employee_name,
    m.employee_name AS manager_name
FROM employees AS e
LEFT JOIN employees AS m
    ON e.manager_id = m.employee_id
   AND m.employee_name = 'Priya';

These queries answer different questions: the first returns employees managed by Priya, while the second retains all employees and only attaches manager details for matching Priya rows.

20. Best Practices for Writing Self Joins

• Use clear and meaningful aliases such as employee, manager, child, and parent.
• Qualify column names with aliases whenever a column exists in both references.
• Use an explicit ON condition to describe the intended relationship.
• Select only the columns needed for the result rather than using SELECT *.
• Use LEFT JOIN when unmatched rows from the first reference must be preserved.
• Use a unique ID comparison to avoid self-matches and duplicate unordered pairs.
• Check whether NULL values affect your matching conditions.
• Use EXPLAIN to inspect query plans for large tables.
• Consider indexes on columns used for joining and filtering, especially foreign-key-like columns such as manager_id.
• Validate results with sample records before running reports on production data.
• Use explicit column names and descriptive output aliases to make results easier to maintain.
• When the relationship has many levels, consider whether a recursive query or a different data model is more appropriate.

21. Performance Optimization for Self Joins

Self joins can be efficient, but they may become expensive when a table contains many rows or the join condition produces numerous matches.

21.1 Create Appropriate Indexes

For the employee-manager example, the primary key on employee_id already provides an index for manager lookups. An additional index on manager_id may help queries that locate employees by manager.

CREATE INDEX idx_employees_manager_id
ON employees(manager_id);

Index usefulness depends on table size, selectivity, the query, and the execution plan. Avoid creating redundant indexes without evaluating their benefit.

21.2 Select Required Columns Only

Returning unnecessary columns can increase data transfer and processing costs. Prefer selecting the fields needed for the report.

SELECT
    e.employee_name,
    m.employee_name AS manager_name
FROM employees AS e
LEFT JOIN employees AS m
    ON e.manager_id = m.employee_id;

21.3 Inspect the Execution Plan

EXPLAIN
SELECT
    e.employee_name,
    m.employee_name AS manager_name
FROM employees AS e
LEFT JOIN employees AS m
    ON e.manager_id = m.employee_id;

EXPLAIN provides information about how MySQL plans to execute the query. Review access methods, possible and selected indexes, estimated rows, and other plan details. The best optimization depends on the actual schema and data distribution.

21.4 Avoid Unnecessary Pair Generation

If the objective is to find pairs of employees in the same department, an unrestricted self join generates far more combinations than needed. Add the shared-department condition and a unique-pair condition such as e1.employee_id < e2.employee_id.

22. Practical Project: Employee and Manager Report

Project objective: Build a report that displays employees, their managers, department details, and the number of direct reports for each manager.

22.1 Requirements

• Use the sample employees table.
• Display every employee and their manager, if one exists.
• Show the employee's department.
• Count the number of direct reports for each employee.
• Display employees without managers.
• Sort the final output by employee ID.

22.2 Query to Display Employee and Manager Details

SELECT
    e.employee_id,
    e.employee_name,
    e.department,
    COALESCE(m.employee_name, 'No Manager') AS manager_name
FROM employees AS e
LEFT JOIN employees AS m
    ON e.manager_id = m.employee_id
ORDER BY e.employee_id;

22.3 Query to Count Direct Reports

SELECT
    m.employee_id,
    m.employee_name,
    COUNT(e.employee_id) AS direct_reports
FROM employees AS m
LEFT JOIN employees AS e
    ON e.manager_id = m.employee_id
GROUP BY m.employee_id, m.employee_name
ORDER BY direct_reports DESC, m.employee_id;

22.4 Expected Learning Outcome

By completing this project, learners practice using aliases, join conditions, outer joins, aggregate functions, grouping, sorting, and NULL handling in one practical scenario.

23. Practice Exercises

Complete the following exercises using the sample tables created in these notes.

1. Write a self join query that displays each employee and their manager.
2. Modify the query to display all employees, including those without managers.
3. Find all employees who report directly to Priya.
4. Find pairs of employees working in the same department without returning duplicate pairs.
5. Display employee pairs in the same department where one employee earns more than the other.
6. Find customers who live in the same city.
7. Compare products in the same category and display their prices.
8. Count the direct reports for each employee.
9. Display an employee, their direct manager, and their senior manager.
10. Use EXPLAIN to inspect the execution plan of an employee-manager self join.

23.1 Challenge Exercise

Write a query that displays each manager's name, department, and the number of employees reporting directly to them. Include managers with zero direct reports and sort the results from the highest number of reports to the lowest.

24. Interview Questions and Answers

Q1. What is a self join in MySQL?

Answer: A self join is a query technique that joins a table to itself to compare rows or retrieve related information from different rows in the same table.

Q2. Does MySQL have a dedicated SELF JOIN keyword?

Answer: No. A self join uses an ordinary join such as INNER JOIN or LEFT JOIN, with the same table referenced using different aliases.

Q3. Why are aliases used in self joins?

Answer: Aliases distinguish the different references to the same table and make it clear which role each column plays.

Q4. What is the difference between INNER JOIN and LEFT JOIN in a self join?

Answer: INNER JOIN returns only rows with matching records. LEFT JOIN preserves every row from the first reference and returns NULL for unmatched columns from the second reference.

Q5. How can you display an employee's manager name?

Answer: Join the employees table to itself using the employee's manager ID and the manager's employee ID.

SELECT
    e.employee_name,
    m.employee_name AS manager_name
FROM employees AS e
LEFT JOIN employees AS m
    ON e.manager_id = m.employee_id;

Q6. How do you prevent duplicate pairs in a self join?

Answer: Use a unique ordering condition such as t1.id < t2.id. This excludes self-matches and ensures that each unordered pair is returned only once when IDs are unique.

Q7. Can a self join be used to find duplicate records?

Answer: Yes. It can identify rows with matching attributes such as email addresses or phone numbers. However, matching attributes alone do not always prove that two records are duplicates.

Q8. Can a self join use multiple conditions?

Answer: Yes. Conditions can be combined using AND and OR to define the relationship more precisely.

Q9. Can a table be joined to itself more than twice?

Answer: Yes. The same table can be referenced multiple times with a unique alias for each reference, such as employee, manager, and senior manager.

Q10. What is the main disadvantage of a self join?

Answer: Incorrect or overly broad join conditions can generate many rows and make queries expensive. Careful aliasing, appropriate conditions, and indexing can help.

Q11. Can a self join compare salaries?

Answer: Yes. It can compare an employee's salary with their manager's salary or compare salaries between employees in the same department.

Q12. Can self joins retrieve a complete hierarchy of unlimited depth?

Answer: Not with a fixed number of ordinary joins alone. Each additional fixed level requires another table reference. A recursive common table expression or another hierarchical data approach may be more suitable for arbitrary-depth relationships.

25. Quick Revision Checklist

• Understand that a self join joins a table to itself.
• Remember that MySQL uses regular join syntax for self joins.
• Assign a distinct alias to every reference to the same table.
• Use an appropriate ON condition to match related rows.
• Choose INNER JOIN or LEFT JOIN based on whether unmatched rows must be included.
• Use qualified column names to avoid ambiguity.
• Use unique ID comparisons to prevent self-matches and duplicate pairs.
• Combine self joins with WHERE, ORDER BY, LIMIT, and aggregate functions as needed.
• Consider NULL values and the difference between filtering in ON and WHERE.
• Use indexes and EXPLAIN when optimizing larger queries.
• Validate results using known sample records.

26. Conclusion

Self joins are an important MySQL technique for comparing records and retrieving relationships stored within a single table. By using aliases and a suitable join condition, you can display employees with their managers, identify customers in the same city, compare products, analyze salary differences, and detect records with matching attributes.

The key to writing a correct self join is understanding the role of each table alias and defining precisely how rows should match. Choose the join type according to whether unmatched records should be included, avoid unnecessary row combinations, and use clear column names and suitable indexes for maintainable, efficient queries.

For further learning, visit the MySQL Training Course or register for a course demo.